﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlServerCe;
using PerCiGal.Types;

namespace PerCiGal.DAL
{
    public class DALMovie
    {
        private readonly Types.COLLECTION_INFO _collectionInfo;

        private readonly string _connStr;

        #region public method

        public DALMovie(Types.COLLECTION_INFO collectionInfo)
        {
            if (collectionInfo == null) throw new ArgumentNullException("collectionInfo");

            this._collectionInfo = collectionInfo;

            this._connStr = DALHelper.GetConnectionString(_collectionInfo.FILE_PATH);
        }

        public int MovieDetailsInsert(Types.MOVIE_DETAILS movie)
        {
            if (movie == null) throw new ArgumentNullException("movie");

            using (var conn = new SqlCeConnection(_connStr))
            {
                try
                {
                    conn.Open();

                    string commandText = DALHelper.GetsqlCommandText("Insert.MOVIE_DETAILS.sqlce");

                    using (var cmd = new SqlCeCommand(commandText, conn))
                    {
                        cmd.Parameters.AddRange(GetMovieDetailsParam(movie));

                        cmd.ExecuteNonQuery();

                        commandText = "Select cast(@@identity as int)";

                        cmd.CommandText = commandText;

                        movie.COD_MOVIE = (int)cmd.ExecuteScalar();

                        if (movie.GENRES != null)
                        {
                            commandText = DALHelper.GetsqlCommandText("Insert.MOVIE_GENRES.sqlce");

                            cmd.CommandText = commandText;

                            foreach (var genre in movie.GENRES)
                            {
                                cmd.Parameters.Clear();
                                cmd.Parameters.Add(SqlceHelper.ParamObj("@cod_movie", movie.COD_MOVIE));
                                cmd.Parameters.Add(SqlceHelper.ParamObj("@cod_genres", genre));

                                cmd.ExecuteNonQuery();
                            }
                        }

                        if (movie.NATIONS != null)
                        {
                            commandText = DALHelper.GetsqlCommandText("Insert.MOVIE_GENRES.sqlce");

                            cmd.CommandText = commandText;

                            foreach (var nation in movie.NATIONS)
                            {
                                cmd.Parameters.Clear();
                                cmd.Parameters.Add(SqlceHelper.ParamObj("@cod_movie", movie.COD_MOVIE));
                                cmd.Parameters.Add(SqlceHelper.ParamObj("@cod_genres", nation));

                                cmd.ExecuteNonQuery();
                            }
                        }
                    }

                    return movie.COD_MOVIE;

                }
                catch (Exception exc)
                {
                    throw exc;
                }
                finally
                {
                    conn.Close();
                }
            }
        }

        public void MovieDetailsUpdate(Types.MOVIE_DETAILS movie)
        {
            if (movie == null) throw new ArgumentNullException("movie");

            using (var conn = new SqlCeConnection(_connStr))
            {
                try
                {
                    conn.Open();

                    string commandText = DALHelper.GetsqlCommandText("Update.MOVIE_DETAILSBYCOD_MOVIE.sqlce");

                    using (var cmd = new SqlCeCommand(commandText, conn))
                    {
                        cmd.Parameters.AddRange(GetMovieDetailsParam(movie));

                        cmd.ExecuteNonQuery();
                    }

                }
                catch (Exception exc)
                {
                    throw exc;
                }
                finally
                {
                    conn.Close();
                }
            }
        }

        public void MovieDetailsDeleteByCodMovie(int codMovie)
        {
            //TODO
        }

        public Types.MOVIE_LIST MovieListSelectAll()
        {
            using (var conn = new SqlCeConnection(_connStr))
            {
                try
                {
                    var movieList = new MOVIE_LIST();

                    movieList.Items = new List<MOVIE_PARTIAL_DETAILS>();

                    conn.Open();

                    string commandText = DALHelper.GetsqlCommandText("Select.MOVIE_DETAILS_ALL.sqlce");

                    using (var cmd = new SqlCeCommand(commandText, conn))
                    {
                        SqlCeDataReader myDataReader = cmd.ExecuteReader();

                        while (myDataReader.Read())
                        {
                            var moviePartialDetails = new MOVIE_PARTIAL_DETAILS();

                            moviePartialDetails.COD_MOVIE = SqlceHelper.DataReaderInt(myDataReader, "COD_MOVIE");

                            moviePartialDetails.MAIN_TITLE = SqlceHelper.DataReaderString(myDataReader, "MAIN_TITLE");

                            movieList.Items.Add(moviePartialDetails);
                        }
                    }

                    return movieList;
                }
                catch (Exception exc)
                {
                    throw exc;
                }
                finally
                {
                    conn.Close();
                }
            }
        }

        public Types.MOVIE_DETAILS MovieDetailsSelectByCodMovie(int codMovie)
        {
            using (var conn = new SqlCeConnection(_connStr))
            {
                try
                {
                    var movie = new MOVIE_DETAILS();

                    conn.Open();

                    string commandText = DALHelper.GetsqlCommandText("Select.MOVIE_DETAILS_BYCOD_MOVIE.sqlce");

                    using (var cmd = new SqlCeCommand(commandText, conn))
                    {
                        cmd.Parameters.AddWithValue("COD_MOVIE", codMovie);

                        SqlCeDataReader myDataReader = cmd.ExecuteReader();

                        while (myDataReader.Read())
                        {
                            movie.COD_MOVIE = SqlceHelper.DataReaderInt(myDataReader, "COD_MOVIE");
                            movie.MAIN_TITLE = SqlceHelper.DataReaderString(myDataReader, "MAIN_TITLE");
                            movie.MINOR_TITLE = SqlceHelper.DataReaderString(myDataReader, "MINOR_TITLE");
                            movie.DISTRIBUTION = SqlceHelper.DataReaderString(myDataReader, "DISTRIBUTION"); 
                            movie.DT_CREATION = SqlceHelper.DataReaderDateTime(myDataReader, "DT_CREATION"); 
                            movie.DT_LAST_MODIFIED = SqlceHelper.DataReaderDateTime(myDataReader, "DT_LAST_MODIFIED"); 
                            movie.DURATION = SqlceHelper.DataReaderShortNullable(myDataReader, "DURATION"); 
                            //movie.GENRES = SqlceHelper.DataReaderString(myDataReader, "GENRES"); 
                            movie.I_HAVE_IT = SqlceHelper.DataReaderBoolean(myDataReader, "I_HAVE_IT");
                            movie.I_WATCHED_IT = SqlceHelper.DataReaderBoolean(myDataReader, "I_WATCHED_IT"); 
                            //movie.NATIONS = SqlceHelper.DataReaderString(myDataReader, "NATIONS"); 
                            movie.NOTE = SqlceHelper.DataReaderString(myDataReader, "NOTE"); 
                            movie.OFFICIAL_SITE = SqlceHelper.DataReaderString(myDataReader, "OFFICIAL_SITE"); 
                            movie.ORIGINAL_TITLE = SqlceHelper.DataReaderString(myDataReader, "ORIGINAL_TITLE"); 
                            movie.PERSONAL_FIELD_1_DESCRIPTION = SqlceHelper.DataReaderString(myDataReader, "PERSONAL_FIELD_1_DESCRIPTION"); 
                            movie.PERSONAL_FIELD_1_VALUE = SqlceHelper.DataReaderString(myDataReader, "PERSONAL_FIELD_1_VALUE"); 
                            movie.PERSONAL_FIELD_2_DESCRIPTION = SqlceHelper.DataReaderString(myDataReader, "PERSONAL_FIELD_2_DESCRIPTION"); 
                            movie.PERSONAL_FIELD_2_VALUE = SqlceHelper.DataReaderString(myDataReader, "PERSONAL_FIELD_2_VALUE"); 
                            movie.PERSONAL_FIELD_3_DESCRIPTION = SqlceHelper.DataReaderString(myDataReader, "PERSONAL_FIELD_3_DESCRIPTION"); 
                            movie.PERSONAL_FIELD_3_VALUE = SqlceHelper.DataReaderString(myDataReader, "PERSONAL_FIELD_3_VALUE"); 
                            movie.PERSONAL_FIELD_4_DESCRIPTION = SqlceHelper.DataReaderString(myDataReader, "PERSONAL_FIELD_4_DESCRIPTION"); 
                            movie.PERSONAL_FIELD_4_VALUE = SqlceHelper.DataReaderString(myDataReader, "PERSONAL_FIELD_4_VALUE"); 
                            movie.PLOT = SqlceHelper.DataReaderString(myDataReader, "PLOT"); 
                            movie.POSTER = (byte[]) SqlceHelper.DataReaderObject(myDataReader, "POSTER"); 
                            movie.PRG_IDENTITY = SqlceHelper.DataReaderIntNullable(myDataReader, "PRG_IDENTITY"); 
                            movie.PRODUCTION = SqlceHelper.DataReaderString(myDataReader, "PRODUCTION");
                            movie.RATE = SqlceHelper.DataReaderIntNullable(myDataReader, "RATE"); 
                            movie.RELEASE_DATE = SqlceHelper.DataReaderDateTimeNullable(myDataReader, "RELEASE_DATE"); 
                            movie.RELEASE_YEAR = SqlceHelper.DataReaderShortNullable(myDataReader, "RELEASE_YEAR"); 
                            movie.SOURCE = SqlceHelper.DataReaderString(myDataReader, "SOURCE"); 
                            movie.SOURCE_LINK = SqlceHelper.DataReaderString(myDataReader, "SOURCE_LINK"); 
                            movie.TRAILER_PATH = SqlceHelper.DataReaderString(myDataReader, "TRAILER_PATH"); 
                        }
                    }

                    return movie;
                }
                catch (Exception exc)
                {
                    throw exc;
                }
                finally
                {
                    conn.Close();
                }
            }
        }

        #endregion

        #region private method

        internal SqlCeParameter[] GetMovieDetailsParam(MOVIE_DETAILS movie)
        {
            var result = new List<SqlCeParameter>
                             {
                                 SqlceHelper.ParamString("@main_title", movie.MAIN_TITLE),
                                 SqlceHelper.ParamString("@minor_title", movie.MINOR_TITLE),
                                 SqlceHelper.ParamObj("@rate", movie.RATE),
                                 SqlceHelper.ParamObj("@poster", movie.POSTER),
                                 SqlceHelper.ParamString("@official_site", movie.OFFICIAL_SITE),
                                 SqlceHelper.ParamString("@trailer_path", movie.TRAILER_PATH),
                                 SqlceHelper.ParamString("@plot", movie.PLOT),
                                 SqlceHelper.ParamString("@note", movie.NOTE),
                                 SqlceHelper.ParamString("@original_title", movie.ORIGINAL_TITLE),
                                 SqlceHelper.ParamString("@release_year", movie.RELEASE_YEAR),
                                 SqlceHelper.ParamString("@release_date", movie.RELEASE_DATE),
                                 SqlceHelper.ParamString("@duration", movie.DURATION),
                                 SqlceHelper.ParamString("@distribution", movie.DISTRIBUTION),
                                 SqlceHelper.ParamString("@production", movie.PRODUCTION),
                                 SqlceHelper.ParamObj("@i_have_it", movie.I_HAVE_IT),
                                 SqlceHelper.ParamObj("@i_watched_it", movie.I_WATCHED_IT),
                                 SqlceHelper.ParamString("@prg_identity", movie.PRG_IDENTITY),
                                 SqlceHelper.ParamString("@source", movie.SOURCE),
                                 SqlceHelper.ParamString("@source_link", movie.SOURCE_LINK),
                                 SqlceHelper.ParamString("@personal_field_1_description",
                                                         movie.PERSONAL_FIELD_1_DESCRIPTION),
                                 SqlceHelper.ParamString("@personal_field_1_value", movie.PERSONAL_FIELD_1_VALUE),
                                 SqlceHelper.ParamString("@personal_field_2_description",
                                                         movie.PERSONAL_FIELD_2_DESCRIPTION),
                                 SqlceHelper.ParamString("@personal_field_2_value", movie.PERSONAL_FIELD_2_VALUE),
                                 SqlceHelper.ParamString("@personal_field_3_description",
                                                         movie.PERSONAL_FIELD_3_DESCRIPTION),
                                 SqlceHelper.ParamString("@personal_field_3_value", movie.PERSONAL_FIELD_3_VALUE),
                                 SqlceHelper.ParamString("@personal_field_4_description",
                                                         movie.PERSONAL_FIELD_4_DESCRIPTION),
                                 SqlceHelper.ParamString("@personal_field_4_value", movie.PERSONAL_FIELD_4_VALUE),
                                 SqlceHelper.ParamDateTime("@dt_last_modified", movie.DT_LAST_MODIFIED),
                                 SqlceHelper.ParamDateTime("@dt_creation", movie.DT_CREATION),
                                 SqlceHelper.ParamObj("@cod_movie", movie.COD_MOVIE)
                             };

            return result.ToArray();
        }

        #endregion

    }
}
